Before computers, numerical analyses, particularly financial ones, were usually prepared on an accountant's columnar pad or spreadsheet, with pencil and calculator in hand. By organising data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the preparer, manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of “electronic spreadsheets.” Better known simply as “spreadsheets,” these software programs provide a computerised replacement for the traditional financial modelling tools: the accountant's columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what word processors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant's columnar pad, thus providing a visible calculator for a user. Because this “pad” exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, a letter to each column, and another letter to each sheet (or page) of the spreadsheet. To reference a location at column A and row 1 of the second page (i.e., the upper-left hand corner), for example, the user types in “B:A1”. In this manner, the spreadsheet defines an addressable storage location or “cell” at each intersection of a row with a column within a given page.
Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant's pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or “formulas” specifying calculations to be performed on the numbers stored in spreadsheet cells. Such spreadsheet cells can also be defined and named as a range as long as they are arranged as a convex set of cells. A typical example of such a named range simply corresponds to a regular table found in an accountant's pad. In this fashion, range names can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature. For an overview, see e.g., Cobb, S., Using Quattro Pro 2, Borland-OsbomeIMcGraw-Mll, 1990; and LeBlond, G. and Cobb, D., Using 1-2-3, Que corp., 1985. The disclosures of each of the foregoing are hereby incorporated by reference.
Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform “what-if” scenarios. After a set of computational relationships has been entered into a worksheet, thanks to imbedded formulas for instance, the spread of information can be recalculated using different sets of assumptions, with the results of each recalculation appearing almost instantaneously. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made. Thus, electronic spreadsheet systems were invented to solve “what-if” problems, that is, changing an input and seeing what happens to an output.
Conventional spreadsheets come today with some imbedded facilities allowing the spreadsheet user to develop his/her own tools addressing his/her own specific needs. The most popular facility of that kind is known as the “macro” language which corresponds to a programming language allowing to automate tasks that the user would instead perform manually. Such a language can be used advantageously when the manipulations performed within a spreadsheet correspond to repetitive steps which can be “packaged” within automated macros. Besides the gain in efficiency provided by such macros, the macros present also the advantage of reducing, if not eliminating, user errors during spreadsheet manipulations. Indeed as the successive steps of a given macro will be followed each time the macro is invoked, any correction applied to a problem found during the execution of the macro will be recorded in the macro, and thus reapplied later on for each new call of the macro. Besides the macro language itself, the macro facility often includes an integrated macro development environment aimed to facilitate the development of macros or the sharing of macros between different spreadsheets. Such environments are typically based on object oriented (OO) techniques which are today the de facto standard for software engineering.
When macros are developed in an electronic spreadsheet, one of the typical objects manipulated by the macro language corresponds to spreadsheet cells which can be arranged as ranges of cells, or more generally to what we call series of cells. A range of cells can be defined as a convex set of cells. A series of cells can be defined as a set of convex sets of cells. When objects such as series of cells are part of the logic of a macro, it is common to have to compare two different objects of this type. This type of comparison aims at determining if a first series of cells is either disjoined from, or equal to, or included in, or overlapping with, or including a second series of cells. The conventional techniques used to perform this type of comparison, such as the ones documented in the technical literture, rely on a geometrical representation of cells within an electronic spreadsheet. For instance a sheet within a electronic spreadsheet can be formally represent by a geometrical plan, with two coordinates. Any cell belonging to this sheet is associated with a row coordinate and with a column coordinate. With such a formal representation, the comparison of two ranges of cells (named “A” and “B”) within a given sheet can be performed with a macro of the following kind:
REM Work with rowsif A.StartRow<B.StartRow thenif B.StartRow<A.EndRow thenif A.EndRow<B.EndRow then Row=OVERLAP else Row=B_IN_Aelseif B.StartRow>A.EndRow then Row=DISJOINED elseRow=OVERLAPendifelseif A.StartRow>B.StartRow thenif A.StartRow<B.EndRow thenif B.EndRow<A.EndRow then Row=OVERLAP else Row=A_IN_Belseif A.StartRow>B.EndRow then Row=DISJOINED elseRow=OVERLAPendifelseif A.EndRow<B.EndRow thenRow=A_IN_Belseif B.EndRow<A.EndRow then Row=B_IN_Aelse Row=SAMEendifendifendifREM work with columnsif A.StartCol<B.StartCol thenif B.StartCol<A.EndCol thenif A.EndCol<B.EndCol then Col=OVERLAP else Col=B_IN_Aelseif B.StartCol>A.EndCol then Col=DISJOINED elseCol=OVERLAPendifelseif A.StartCol>B.StartCol thenif A.StartCol<B.EndCol thenif B.EndCol<A.EndCol then Col=OVERLAP else Col=A_IN_Belseif A.StartCol>B.EndCol then Col=DISJOINED elseCol=OVERLAPendifelseif A.EndCol<B.EndCol thenCol=A_IN_Belseif B.EndCol<A.EndCol then Col=B_IN_Aelse Col=SAMEendifendifendifREM get the final comparison on rangeif Col=DISJOINED OR Row=DISJOINED thenRange=DISJOINEDelseif Col=OVERLAP OR Row=OVERLAP thenRange=OVERLAPelseif Col=SAME thenRange=Rowelseif Row=SAME thenRange=Colelseif Row=Col then Range=Row else Range=OVERLAPendifendifendifendif
The above program is not very complex and relatively fast to execute (the longest path corresponding to 13 statements to execute), but it only performs the comparison of two ranges of cells in a two-dimensionnal spreadsheet. When ranges of cells must be compared in a three-dimensionnal spreadsheet (as it is the case for conventional electronic spreadsheets available on the market), then its complexity remains at the same level, but the execution time is increased. With multi-dimensionnal environments containing more than 3 dimensions, the execution time may become excessive.
If now series of cells have to be compared, then another level of complexity is introduced. Let illustrates it with a simple example in a two-dimensionnal spreadsheet: two series of cells named “A” and “B”, each constituted by two ranges of cells respectively named “A1”, “A2” and “B1”, “B2”, are compared to determine if the series of cells “A” is either disjoined from, or equal to, or included in, or overlapping with, or including the series of cells “B”. As “A1”, “A2”, “B1” and “B2” are ranges of cells, a natural thought process would be to first compare each of the ranges “A1” and “A2” with each of the ranges “B1” and “B2”, and then to perform some logic based on the results of the four range comparisons (“A1” vs “B1”, “A1” vs “B2”, “A2” vs “B1”, and “A2” vs “B2”). It turns out that this method cannot properly resolve some situations, as illustrated in FIG. 3A and in FIG. 3B. Within FIG. 3A are represented the four ranges “A1”, “A2” (both shown as rectangles with solid lines), and “B1”, “B2” (both shown as rectangles with dashed lines). The comparison between the ranges belonging to the series “A” and the ranges belonging to the series “B” gives the following result, by using obvious notations: Comp (A1,B1)=OVERLAP; Comp (A1,B2)=DISJOINED; Comp (A2,B1)=OVERLAP; Comp (A2,B2)=B_IN_A. As far as the series “A” and “B” are concerned, the comparison is Comp (A,B)=B_IN_A because all the cells belonging to the series B belong also to the series A while the reverse is not true. Looking now at the FIG. 3B, the ranges “A1”, “A2”, “B1” and “B2” compare between them the same way (Comp (A1,B1)=OVERLAP; Comp (A1,B2)=DISJOINED; Comp (A2,B1)=OVERLAP; Comp (A2,B2)=B_IN_A) but the comparison between the series “A” and “B” is Comp (A,B)=OVERLAP because there exists some cells of the series B belonging to the series A and others not belonging to the series A. To discriminate between the two cases illustrated in the FIG. 3A and in the FIG. 3B, some additional logic must be introduced on top of the individual range comparison, moving the complexity and the efficiency of this conventional approach to unacceptable limits.
The present invention defines a system and a method for resolving the above mentioned problem in a way which first does not depend on the number of dimensions defined in the working environment, and which second does not depend on the structure of the objects to be compared. The present invention takes advantage of the inheritance properties available in object oriented environments.